package com.business.web.spring;


import com.auth.domain.T_user;
import com.auth.service.T_userService;
import com.business.domain.*;
import com.business.service.*;
import com.common.db.JsonVO;
import com.common.db.Pg;
import com.common.utils.*;
import com.system.service.LxConstans;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;
import java.util.concurrent.atomic.DoubleAccumulator;

@Controller
@RequestMapping("/excelController")

public class ExcelController {
    private static Logger log = Logger.getLogger(ExcelController.class.getName());

    @Resource
    private T_newsService t_newsService;
    @Resource
    private T_goodsService t_goodsService;
    @Resource
    private T_order_goodsService t_order_goodsService;
    @Resource
    private T_contract_billService t_contract_billService;
    @Resource
    private T_buildingService t_buildingService;
    @Resource
    private T_building_houseService t_building_houseService;
    @Resource
    private T_userService t_userService;
    @Resource
    private T_contractService t_contractService;


    /**
     * 跳转至上传文件页面
     * business/t_news/t_newsMinpianUpload.jsp
     */
    @RequestMapping(value = "/toExcelUploadMingpian")
    public String toExcelUploadMingpian() {
        log.debug("------跳转到/WEB-INF/views/business/t_news/t_newsMinpianUpload.jsp------");
        return "business/t_news/t_newsMinpianUpload";
    }

    @RequestMapping(value = "/toExcelUploadNenghao")
    public String toExcelUploadNenghao() {
        log.debug("------跳转到/WEB-INF/views/business/t_contract_bill/t_contract_billUpload.jsp------");
        return "business/t_contract_bill/t_contract_billUpload";
    }

    @RequestMapping(value = "/toExcelUploadZhaopin")
    public String toExcelUploadZhaopin() {
        log.debug("------跳转到/WEB-INF/views/business/t_news/t_newsZhaopinUpload.jsp------");
        return "business/t_news/t_newsZhaopinUpload";
    }
    @RequestMapping(value = "/toExcelUploadUser")
    public String toExcelUploadUser() {
        log.debug("------跳转到/WEB-INF/views/auth/t_user/t_userUpload.jsp------");
        return "auth/t_user/t_userUpload";
    }

    @RequestMapping(value = "/toExcelUploadLoudong")
    public String toExcelUploadLoudong() {
        log.debug("------跳转到/WEB-INF/views/business/t_building/t_buildingUpload.jsp------");
        return "business/t_building/t_buildingUpload";
    }
    @RequestMapping(value = "/toExcelUploadFanghao")
    public String toExcelUploadFanghao() {
        log.debug("------跳转到/WEB-INF/views/business/t_building_house/t_building_houseUpload.jsp------");
        return "business/t_building_house/t_building_houseUpload";
    }

    /**
     * 上传导入员工excel数据，并且返回到员工管理页面；
     * 针对提交过来的页面使用隐藏ifreame方式的页面，返回时直接返回到提交页面，在提交页面判断是否从左侧菜单进入还是提交后进入；不能返回json对象；
     * @param request
     * @param response
     * @param uploadExcelFile
     * @return
     */
    @RequestMapping("/excelUploadUser")
    public String excelUploadUser(HttpServletRequest request, HttpServletResponse response, MultipartFile uploadExcelFile) {
        String returnurl="auth/t_user/t_userUpload";
        String tempBathPath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "" + request.getContextPath() + "/";
        //设置操作员为登录账号
        HttpSession session = request.getSession(true);
        T_user sys_user = (T_user) session.getAttribute(SystemConstants.SESSIONNAME_OBJECT);

        JsonVO vo = new JsonVO();
        vo.setResult("error");
        vo.setJieguo(false);
        List readExcelRowList = null;
        String resultString = "error";

        try {
            String originalFileName = uploadExcelFile.getOriginalFilename();
            int beginIndex = originalFileName.lastIndexOf(".");
            //原名
            String fileName = originalFileName.substring(0, beginIndex);
            log.info("------fileName文件名=" + fileName.toString() + ";file 文件名后缀=" + originalFileName.substring(beginIndex));
            //excel内容转成vo list；插入表中；
            T_user t_user = null;
            //excel row list
            //调用util方法拿到解析的数据集合,从第二行开始
            readExcelRowList = new ReadExcelUtil().readExcel(uploadExcelFile);
            //结果如下形式readExcel=[[5, , 7, 8, , ], [, 6, , ]]
            log.info("------readExcel行内容=" + readExcelRowList.toString());
            List listVo = readExcelRowList;// excelContentToVo(readExcel);
            if (listVo != null) {
                for (int i = 0; i < listVo.size(); i++) {
                    log.info("------excel第"+i+"行内容（不含标题行）=" + listVo.get(i).toString());
                    // one row cell value arraylist;
                    List tods = (ArrayList) listVo.get(i);
                    t_user = new T_user();
                    //tods  姓名	部门	职位	入职时间	身份证号码
                    // 联系电话	性别	学历	资格证书	籍贯
                    // 户籍地址 常住地址	户籍类型	婚姻状态	劳动关系类型
                    // 合同类型  合同开始日期	合同终止日期	试用期止	续签开始日期
                    // 续签终止日期  公积金账号	银行卡号	社保公积金起缴时间	退保月份
                    // 离职日期  社会工龄	工龄 	出生日期

                    //1:  姓名
                    if (tods.size() > 0) {
                        if(tods.get(0).toString()=="" || tods.get(0).toString()==null){
                            log.error("------readExcel 遇到错误: 姓名不能为空！");
                            int rowno = i+1;
                            resultString = "上传的excel数据遇到错误: 第"+rowno+"行，“姓名”不能为空！该行之前的数据正常导入到系统中！";
                            return returnurl;
                        }else {
                            t_user.setUsername(tods.get(0).toString());
                        }
                    }

                    //先查询是否有该员工信息存在，如果存在则更新，否则插入
                    T_user temp = new T_user();
                    temp.setUsername(t_user.getUsername());
                    temp.setPageSize(100);
                    temp.setStartNo(0);
                    temp.setEndNo(100);
                    List list = t_userService.findAll(temp);
                    if(list!=null&&list.size()>0) {
                        t_user = (T_user) list.get(0); //如果已经存在，则只更新在exel中有的属性值，否则还是用数据中属性值；
                    }

                    //2:  部门
                    if (tods.size() > 1) {
                        t_user.setBranchid (tods.get(1).toString());
                    }
                    //3:  职位
                    if (tods.size() > 2) {
                        t_user.setExt2 (tods.get(2).toString());
                    }
                    //4:  入职时间
                    if (tods.size() > 3) {
                        t_user.setJoinday (tods.get(3).toString());
                    }
                    //5:  身份证号码
                    if (tods.size() > 4) {
                        t_user.setPersonid ( tods.get(4).toString() );
                    }

                    //6:  联系电话
                    if (tods.size() > 5) {
                        t_user.setPhone(tods.get(5).toString());
                    }
                    //7:  性别
                    if (tods.size() > 6) {
                        t_user.setSex( tods.get(6).toString());
                    }
                    //8:  学历
                    if (tods.size() > 7 && tods.get(7) != null && tods.get(7) != "") {
                        t_user.setXueli (tods.get(7).toString());
                    }
                    //9:  资格证书
                    if (tods.size() > 8) {
                        t_user.setZhengshu (tods.get(8).toString());
                    }
                    //10:  籍贯
                    if (tods.size() > 9) {
                        t_user.setJiguan (tods.get(9).toString());
                    }

                    //11:  户籍地址
                    if (tods.size() > 10) {
                        t_user.setHujidizhi(tods.get(10).toString());
                    }
                    //12:  常住地址
                    if (tods.size() > 11) {
                        t_user.setAddress( tods.get(11).toString());
                    }
                    //13:  户籍类型
                    if (tods.size() > 12 && tods.get(12) != null && tods.get(12) != "") {
                        t_user.setHujitype (tods.get(12).toString());
                    }
                    //14:  婚姻状态	;
                    if (tods.size() > 13) {
                        t_user.setMarrystatus (tods.get(13).toString());
                    }
                    //15:  劳动关系类型
                    if (tods.size() > 14) {
                        t_user.setRelationtype (tods.get(14).toString());
                    }

                    //16:  合同类型
                    if (tods.size() > 15) {
                        t_user.setContracttype(tods.get(15).toString());
                    }
                    //17:  合同开始日期
                    if (tods.size() > 16) {
                        t_user.setContarctstartdate (tods.get(16).toString());
                    }
                    //18:  合同终止日期
                    if (tods.size() > 17) {
                        t_user.setContractenddate (tods.get(17).toString());
                    }
                    //19:  试用期止
                    if (tods.size() > 18) {
                        t_user.setTrydate (tods.get(18).toString());
                    }
                    //20:  续签开始日期
                    if (tods.size() > 19) {
                        t_user.setResignstartdate ( tods.get(19).toString() );
                    }

                    //21:  续签终止日期
                    if (tods.size() > 20) {
                        t_user.setResignenddate(tods.get(20).toString());
                    }
                    //22:  公积金账号
                    if (tods.size() > 21) {
                        t_user.setGongjiji( tods.get(21).toString());
                    }
                    //23:  银行卡号
                    if (tods.size() > 22 && tods.get(22) != null && tods.get(22) != "") {
                        t_user.setCardno (tods.get(22).toString());
                    }
                    //24:  社保公积金起缴时间
                    if (tods.size() > 23) {
                        t_user.setDuestartdate (tods.get(23).toString());
                    }
                    //25:  退保月份
                    if (tods.size() > 24) {
                        t_user.setQuitduedate (tods.get(24).toString());
                    }

                    //26:  离职日期
                    if (tods.size() > 25) {
                        t_user.setQuitdate(tods.get(25).toString());
                    }
                    //27:  社会工龄
                    if (tods.size() > 26) {
                        t_user.setWorkyear (tods.get(26).toString());
                    }
                    //28:  工龄
                    if (tods.size() > 27) {
                        t_user.setWork (tods.get(27).toString());
                    }
                    //29:  出生日期
                    if (tods.size() > 28) {
                        t_user.setBorndate (tods.get(28).toString());
                    }
                    //30:  在职状态
                    if (tods.size() > 29) {
                        //t_user.setJobstatus ( tods.get(29).toString() );
                    }

                    t_user.setCreatetime(DateUtil.getCurrDateTimeStand()); //yyyy-MM-dd HH:mm:ss
                    t_user.setCreateuserid(sys_user.getUsername());
                    //t_user.setStatus("1"); //初始化 状态：1：正常；0：无效；2：冻结；

                    if(list!=null&&list.size()>0) {
                        t_userService.update(t_user);
                    }else{
                        t_userService.save(t_user);
                    }
                }
            }
            //boolean response = customerInfoService.insterCustomerInfo(readExcel, fileName);
            vo.setResult("success");
            vo.setJieguo(true);
            resultString = "success";
        } catch (Exception e) {
            vo.setResult("false");
            vo.setJieguo(false);
            log.error("------readExcel 遇到错误: " + e.toString());
            resultString = "readExcel 遇到错误: " + e.toString();
            e.printStackTrace();
        } finally {
            //return vo;
            request.setAttribute("vo", vo);
            request.setAttribute("resultString", resultString);
            return returnurl;
        }
    }

    /**
     * 上传导入楼栋excel数据，并且返回到楼栋提交页面；
     * 针对提交过来的页面使用隐藏ifreame方式的页面，返回时直接返回到提交页面，在提交页面判断是否从左侧菜单进入还是提交后进入；不能返回json对象；
     * @param request
     * @param response
     * @param uploadExcelFile
     * @return
     */
    @RequestMapping("/excelUploadLoudong")
    public String excelUploadLoudong(HttpServletRequest request, HttpServletResponse response, MultipartFile uploadExcelFile) {
        String tempBathPath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "" + request.getContextPath() + "/";
        //设置操作员为登录账号
        HttpSession session = request.getSession(true);
        T_user sys_user = (T_user) session.getAttribute(SystemConstants.SESSIONNAME_OBJECT);

        JsonVO vo = new JsonVO();
        vo.setResult("error");
        vo.setJieguo(false);
        List readExcelRowList = null;
        String resultString = "error";

        try {
            String originalFileName = uploadExcelFile.getOriginalFilename();
            int beginIndex = originalFileName.lastIndexOf(".");
            //原名
            String fileName = originalFileName.substring(0, beginIndex);
            log.info("------fileName文件名=" + fileName.toString() + ";file 文件名后缀=" + originalFileName.substring(beginIndex));
            //excel内容转成vo list；插入表中；
            T_building t_building = null;
            //excel row list
            //调用util方法拿到解析的数据集合,从第二行开始
            readExcelRowList = new ReadExcelUtil().readExcel(uploadExcelFile);
            //结果如下形式readExcel=[[5, , 7, 8, , ], [, 6, , ]]
            log.info("------readExcel行内容=" + readExcelRowList.toString());
            List listVo = readExcelRowList;// excelContentToVo(readExcel);
            if (listVo != null) {
                for (int i = 0; i < listVo.size(); i++) {
                    log.info("------excel第"+i+"行内容（不含标题行）=" + listVo.get(i).toString());
                    // one row cell value arraylist;
                    List tods = (ArrayList) listVo.get(i);
                    t_building = new T_building();
                    //tods 楼栋名称	用途	 楼层数	建筑面积（平方米）	公摊率%	招商联系人	招商联系电话
                    //1:  楼栋名称
                    if (tods.size() > 0) {
                        t_building.setName(tods.get(0).toString());
                    }
                    //2:  用途
                    if (tods.size() > 1) {
                        t_building.setUseofbuilding (tods.get(1).toString());
                    }
                    //3:  楼层数
                    if (tods.size() > 2) {
                        t_building.setFloornum( new Integer(tods.get(2).toString()));
                    }
                    //4:  建筑面积（平方米）
                    if (tods.size() > 3) {
                        t_building.setArea (new Double(tods.get(3).toString()));
                    }
                    //5:  公摊率%
                    if (tods.size() > 4) {
                        t_building.setPublicrate ( tods.get(4).toString() );
                    }

                    //6:  招商联系人
                    if (tods.size() > 5) {
                        t_building.setLinkman(tods.get(5).toString());
                    }
                    //7:  招商联系电话
                    if (tods.size() > 6) {
                        t_building.setLinktel( tods.get(6).toString());
                    }

                    t_building.setCreattime(DateUtil.getCurrDateTimeStand()); //yyyy-MM-dd HH:mm:ss
                    t_building.setCreater(sys_user.getUsername());
                    t_building.setBiztype("building" ); //  楼栋 building；
                    t_building.setBuildingtype("1"); //初始化  1
                    //插入数据库
                    t_buildingService.save(t_building);
                }
            }
            //boolean response = customerInfoService.insterCustomerInfo(readExcel, fileName);
            vo.setResult("success");
            vo.setJieguo(true);
            resultString = "success";
        } catch (Exception e) {
            vo.setResult("false");
            vo.setJieguo(false);
            log.error("------readExcel 遇到错误: " + e.toString());
            resultString = "readExcel 遇到错误: " + e.toString();
            e.printStackTrace();
        } finally {
            //return vo;
            request.setAttribute("vo", vo);
            request.setAttribute("resultString", resultString);
            return "business/t_building/t_buildingUpload";
        }
    }

    /**
     * 上传导入房号excel数据，并且返回到房号提交页面；
     * 针对提交过来的页面使用隐藏ifreame方式的页面，返回时直接返回到提交页面，在提交页面判断是否从左侧菜单进入还是提交后进入；不能返回json对象；
     * @param request
     * @param response
     * @param uploadExcelFile
     * @return
     */
    @RequestMapping("/excelUploadFanghao")
    public String excelUploadFanghao(HttpServletRequest request, HttpServletResponse response, MultipartFile uploadExcelFile) {
        String tempBathPath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "" + request.getContextPath() + "/";
        //设置操作员为登录账号
        HttpSession session = request.getSession(true);
        T_user sys_user = (T_user) session.getAttribute(SystemConstants.SESSIONNAME_OBJECT);

        JsonVO vo = new JsonVO();
        vo.setResult("error");
        vo.setJieguo(false);
        List readExcelRowList = null;
        String resultString = "error";

        try {
            String originalFileName = uploadExcelFile.getOriginalFilename();
            int beginIndex = originalFileName.lastIndexOf(".");
            //原名
            String fileName = originalFileName.substring(0, beginIndex);
            log.info("------fileName文件名=" + fileName.toString() + ";file 文件名后缀=" + originalFileName.substring(beginIndex));
            //excel内容转成vo list；插入表中；
            T_building_house t_building_house = null;
            //excel row list
            //调用util方法拿到解析的数据集合,从第二行开始
            readExcelRowList = new ReadExcelUtil().readExcel(uploadExcelFile);
            //结果如下形式readExcel=[[5, , 7, 8, , ], [, 6, , ]]
            log.info("------readExcel行内容=" + readExcelRowList.toString());
            List listVo = readExcelRowList;// excelContentToVo(readExcel);
            if (listVo != null) {
                for (int i = 0; i < listVo.size(); i++) {
                    log.info("------excel第"+i+"行内容（不含标题行）=" + listVo.get(i).toString());
                    // one row cell value arraylist;
                    List tods = (ArrayList) listVo.get(i);
                    t_building_house = new T_building_house();
                    //tods 楼栋名称	房源 	装修	 租赁单价（天/元/㎡）	房源初始时间
                    // 楼层	用途	  面积（㎡）	层高  销售单价（元/㎡）
                    // 上架状态
                    //1:  楼栋名称
                    if (tods.size() > 0) {
                        t_building_house.setBuildingname(tods.get(0).toString());
                    }
                    //2:  房源
                    if (tods.size() > 1) {
                        t_building_house.setHousename (tods.get(1).toString());
                    }
                    //3:  装修
                    if (tods.size() > 2) {
                        t_building_house.setFix (tods.get(2).toString());
                    }
                    //4:  租赁单价
                    if (tods.size() > 3) {
                        t_building_house.setZuilinbaseprice (new Double( tods.get(3).toString()) );
                    }
                    //5:  房源初始时间
                    if (tods.size() > 4) {
                        t_building_house.setExt1 ( tods.get(4).toString() );
                    }

                    //6:  楼层
                    if (tods.size() > 5) {
                        t_building_house.setFloornum(new Integer(tods.get(5).toString()) );
                    }
                    //7:  用途
                    if (tods.size() > 6) {
                        t_building_house.setExt2( tods.get(6).toString());
                    }
                    //8:  面积（㎡）
                    if (tods.size() > 7 && tods.get(7) != null && tods.get(7) != "") {
                        t_building_house.setArea (new Double(tods.get(7).toString()) );
                    }
                    //9:  层高
                    if (tods.size() > 8) {
                        t_building_house.setHight (tods.get(8).toString());
                    }
                    //10:  销售单价（元/㎡）
                    if (tods.size() > 9) {
                        t_building_house.setSalebaseprice (new Double( tods.get(9).toString()) );
                    }

                    //11:  上架状态
                    if (tods.size() > 10) {
                        t_building_house.setShowstatus (tods.get(10).toString());
                    }

                    t_building_house.setBuildingtype("1"); //1：房源；2：车位；
                    t_building_house.setStatus("空置"); //导入即生效，status=已锁定 已租赁 空置 自用 已销售；
                    t_building_house.setExt5(DateUtil.getCurrDateTimeStand()); //yyyy-MM-dd HH:mm:ss
                    t_building_house.setExt6(sys_user.getUsername());
                    t_building_house.setBiztype("房源" ); // 初始化 Mingpian；
                    //插入数据库
                    t_building_houseService.save(t_building_house);
                }
            }
            //boolean response = customerInfoService.insterCustomerInfo(readExcel, fileName);
            vo.setResult("success");
            vo.setJieguo(true);
            resultString = "success";
        } catch (Exception e) {
            vo.setResult("false");
            vo.setJieguo(false);
            log.error("------readExcel 遇到错误: " + e.toString());
            resultString = "readExcel 遇到错误: " + e.toString();
            e.printStackTrace();
        } finally {
            //return vo;
            request.setAttribute("vo", vo);
            request.setAttribute("resultString", resultString);
            return "business/t_building_house/t_building_houseUpload";
        }
    }

    /**
     * 上传导入名片excel数据，并且返回到名片提交页面；
     * 针对提交过来的页面使用隐藏ifreame方式的页面，返回时直接返回到提交页面，在提交页面判断是否从左侧菜单进入还是提交后进入；不能返回json对象；
     * @param request
     * @param response
     * @param uploadExcelFile
     * @return
     */
    @RequestMapping("/excelUploadMingpian")
    //@ResponseBody
    //public JsonVO testexceladd(HttpServletRequest request, HttpServletResponse response, MultipartFile upOrderfile) {
    public String excelUploadMingpian(HttpServletRequest request, HttpServletResponse response, MultipartFile uploadExcelFile) {
        //public JsonVO testexceladd( HttpServletRequest request) {
        // public void saveT_goods(T_goods t_goods, HttpServletRequest request, HttpServletResponse response, @RequestParam("filesZtp") MultipartFile[] files,  @RequestParam("filesXqtp") MultipartFile[] filesXqtp){
        //response.setContentType("text/plain;charset=UTF-8");

        String tempBathPath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "" + request.getContextPath() + "/";
        //设置操作员为登录账号
        HttpSession session = request.getSession(true);
        T_user sys_user = (T_user) session.getAttribute(SystemConstants.SESSIONNAME_OBJECT);

        JsonVO vo = new JsonVO();
        vo.setResult("error");
        vo.setJieguo(false);
        List readExcelRowList = null;
        String resultString = "error";

        try {
            String originalFileName = uploadExcelFile.getOriginalFilename();
            int beginIndex = originalFileName.lastIndexOf(".");
            //原名
            String fileName = originalFileName.substring(0, beginIndex);
            log.info("------fileName文件名=" + fileName.toString() + ";file 文件名后缀=" + originalFileName.substring(beginIndex));
            //excel内容转成vo list；插入表中；
            T_news t_news = null;
            //excel row list
            //调用util方法拿到解析的数据集合,从第二行开始
            readExcelRowList = new ReadExcelUtil().readExcel(uploadExcelFile);
            //结果如下形式readExcel=[[5, , 7, 8, , ], [, 6, , ]]
            log.info("------readExcel行内容=" + readExcelRowList.toString());
            List listVo = readExcelRowList;// excelContentToVo(readExcel);
            if (listVo != null) {
                for (int i = 0; i < listVo.size(); i++) {
                    log.info("------excel第"+i+"行内容（不含标题行）=" + listVo.get(i).toString());
                    // one row cell value arraylist;
                    List tods = (ArrayList) listVo.get(i);
                    t_news = new T_news();
                    //tods 公司	职位	姓名	邮箱	手机	地址1	地址2	网址	电话	备注1	备注2
                    //1:  公司
                    if (tods.size() > 0) {
                        t_news.setTitle(tods.get(0).toString());
                    }
                    //2:  职位
                    if (tods.size() > 1) {
                        t_news.setClasses (tods.get(1).toString());
                    }
                    //3:  姓名
                    if (tods.size() > 2) {
                        t_news.setNewfrom (tods.get(2).toString());
                    }
                    //4:  邮箱
                    if (tods.size() > 3) {
                        t_news.setExt7 (tods.get(3).toString());
                    }
                    //5:  手机
                    if (tods.size() > 4) {
                        t_news.setExt8 ( tods.get(4).toString() );
                    }

                    //6:  地址1
                    if (tods.size() > 5) {
                        t_news.setExt5(tods.get(5).toString());
                    }
                    //7:  地址2
                    if (tods.size() > 6) {
                        t_news.setExt6( tods.get(6).toString());
                    }
                    //8:  网址
                    if (tods.size() > 7 && tods.get(7) != null && tods.get(7) != "") {
                        t_news.setExt9 (tods.get(7).toString());
                    }
                    //9:  电话;
                    if (tods.size() > 8) {
                        t_news.setCq (tods.get(8).toString());
                    }
                    //10:  备注1
                    if (tods.size() > 9) {
                        t_news.setDescriptions (tods.get(9).toString());
                    }
                    //11:  备注2
                    if (tods.size() > 10) {
                        t_news.setLink (tods.get(9).toString());
                    }
                    t_news.setStatus("1"); //导入即生效，status="1"；
                    t_news.setCreattime(DateUtil.getCurrDateTimeStand()); //yyyy-MM-dd HH:mm:ss
                    t_news.setCreater(sys_user.getUsername());
                    t_news.setBiztype("Mingpian" ); // 初始化 Mingpian；
                    t_news.setLevel1name("名片管理"); //初始化
                    t_news.setTypes("Mingpian"); //初始化
                    //插入数据库
                    t_newsService.save(t_news);
                }
            }
            //boolean response = customerInfoService.insterCustomerInfo(readExcel, fileName);
            vo.setResult("success");
            vo.setJieguo(true);
            resultString = "success";
        } catch (Exception e) {
            vo.setResult("false");
            vo.setJieguo(false);
            log.error("------readExcel 遇到错误: " + e.toString());
            resultString = "readExcel 遇到错误: " + e.toString();
            e.printStackTrace();
        } finally {
            //return vo;
            request.setAttribute("vo", vo);
            request.setAttribute("resultString", resultString);
            return "business/t_news/t_newsMinpianUpload";
        }
    }

    /**
     * 上传导入招聘excel数据，并且返回到招聘提交页面；
     * 针对提交过来的页面使用隐藏ifreame方式的页面，返回时直接返回到提交页面，在提交页面判断是否从左侧菜单进入还是提交后进入；不能返回json对象；
     * @param request
     * @param response
     * @param uploadExcelFile
     * @return
     */
    @RequestMapping("/excelUploadZhaopin")
    //@ResponseBody
    //public JsonVO testexceladd(HttpServletRequest request, HttpServletResponse response, MultipartFile upOrderfile) {
    public String excelUploadZhaopin(HttpServletRequest request, HttpServletResponse response, MultipartFile uploadExcelFile) {
        String tempBathPath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "" + request.getContextPath() + "/";
        //设置操作员为登录账号
        HttpSession session = request.getSession(true);
        T_user sys_user = (T_user) session.getAttribute(SystemConstants.SESSIONNAME_OBJECT);

        JsonVO vo = new JsonVO();
        vo.setResult("error");
        vo.setJieguo(false);
        List readExcelRowList = null;
        String resultString = "error";

        try {
            String originalFileName = uploadExcelFile.getOriginalFilename();
            int beginIndex = originalFileName.lastIndexOf(".");
            //原名
            String fileName = originalFileName.substring(0, beginIndex);
            log.info("------fileName文件名=" + fileName.toString() + ";file 文件名后缀=" + originalFileName.substring(beginIndex));
            //excel内容转成vo list；插入表中；
            T_news t_news = null;
            //excel row list
            //调用util方法拿到解析的数据集合,从第二行开始
            readExcelRowList = new ReadExcelUtil().readExcel(uploadExcelFile);
            //结果如下形式readExcel=[[5, , 7, 8, , ], [, 6, , ]]
            log.info("------readExcel行内容=" + readExcelRowList.toString());
            List listVo = readExcelRowList;// excelContentToVo(readExcel);
            if (listVo != null) {
                for (int i = 0; i < listVo.size(); i++) {
                    log.info("------excel第"+i+"行内容（不含标题行）=" + listVo.get(i).toString());
                    // one row cell value arraylist;
                    List tods = (ArrayList) listVo.get(i);
                    t_news = new T_news();
                    //tods 应聘者姓名	应聘岗位	联系电话	邀约状态	邀约面试时间
                    // 面试地点	沟通情况	几面	 备注1	备注2
                    //1:  应聘者姓名
                    if (tods.size() > 0) {
                        t_news.setTitle(tods.get(0).toString());
                    }
                    //2:  应聘岗位
                    if (tods.size() > 1) {
                        t_news.setClasses (tods.get(1).toString());
                    }
                    //3:  联系电话
                    if (tods.size() > 2) {
                        t_news.setLink (tods.get(2).toString());
                    }
                    //4:  邀约状态
                    if (tods.size() > 3) {
                        t_news.setExt6 (tods.get(3).toString());
                    }
                    //5:  邀约面试时间
                    if (tods.size() > 4) {
                        t_news.setExt7 ( tods.get(4).toString() );
                    }

                    //6:  面试地点
                    if (tods.size() > 5) {
                        t_news.setExt8(tods.get(5).toString());
                    }
                    //7:  沟通情况
                    if (tods.size() > 6) {
                        t_news.setExt5( tods.get(6).toString());
                    }
                    //8:  几面
                    if (tods.size() > 7 && tods.get(7) != null && tods.get(7) != "") {
                        t_news.setExt4 (tods.get(7).toString());
                    }
                    //9:  备注1
                    if (tods.size() > 8) {
                        t_news.setDescriptions (tods.get(8).toString());
                    }
                    //10:  备注2;
                    if (tods.size() > 9) {
                        t_news.setExt3 (tods.get(9).toString());
                    }

                    t_news.setStatus("1"); //导入即生效，status="1"；
                    t_news.setCreattime(DateUtil.getCurrDateTimeStand()); //yyyy-MM-dd HH:mm:ss
                    t_news.setCreater(sys_user.getUsername());
                    t_news.setBiztype("Zhaopin" ); // 初始化 Mingpian；
                    t_news.setLevel1name("招聘管理"); //初始化
                    t_news.setTypes("Zhaopin"); //初始化
                    //插入数据库
                    t_newsService.save(t_news);
                }
            }
            //boolean response = customerInfoService.insterCustomerInfo(readExcel, fileName);
            vo.setResult("success");
            vo.setJieguo(true);
            resultString = "success";
        } catch (Exception e) {
            vo.setResult("false");
            vo.setJieguo(false);
            log.error("------readExcel 遇到错误: " + e.toString());
            resultString = "readExcel 遇到错误: " + e.toString();
            e.printStackTrace();
        } finally {
            //return vo;
            request.setAttribute("vo", vo);
            request.setAttribute("resultString", resultString);
            return "business/t_news/t_newsZhaopinUpload";
        }
    }


    /**
     * 上传导入能耗（水电）excel数据，并且返回到物业收款账单页面；
     * 针对提交过来的页面使用隐藏ifreame方式的页面，返回时直接返回到提交页面，在提交页面判断是否从左侧菜单进入还是提交后进入；不能返回json对象；
     * @param request
     * @param response
     * @param uploadExcelFile
     * @return
     */
    @RequestMapping("/excelUploadNenghao")
    //@ResponseBody
    //public JsonVO testexceladd(HttpServletRequest request, HttpServletResponse response, MultipartFile upOrderfile) {
    public String excelUploadNenghao(HttpServletRequest request, HttpServletResponse response, MultipartFile uploadExcelFile) {
        String tempBathPath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "" + request.getContextPath() + "/";
        //设置操作员为登录账号
        HttpSession session = request.getSession(true);
        T_user sys_user = (T_user) session.getAttribute(SystemConstants.SESSIONNAME_OBJECT);

        JsonVO vo = new JsonVO();
        vo.setResult("error");
        vo.setJieguo(false);
        List readExcelRowList = null;
        String resultString = "error";

        try {
            String originalFileName = uploadExcelFile.getOriginalFilename();
            int beginIndex = originalFileName.lastIndexOf(".");
            //原名
            String fileName = originalFileName.substring(0, beginIndex);
            log.info("------fileName文件名=" + fileName.toString() + ";file 文件名后缀=" + originalFileName.substring(beginIndex));
            //excel内容转成vo list；插入表中；
            T_contract_bill t_contract_billSF, t_contract_billDF;
            T_contract t_contract;
            //excel row list
            //调用util方法拿到解析的数据集合,从第二行开始
            readExcelRowList = new ReadExcelUtil().readExcel(uploadExcelFile);
            //结果如下形式readExcel=[[5, , 7, 8, , ], [, 6, , ]]
            log.info("------readExcel行内容=" + readExcelRowList.toString());
            List listVo = readExcelRowList;// excelContentToVo(readExcel);
            if (listVo != null) {
                for (int i = 0; i < listVo.size(); i++) {
                    log.info("------excel第"+i+"行内容（不含标题行）=" + listVo.get(i).toString());
                    // one row cell value arraylist;
                    List tods = (ArrayList) listVo.get(i);
                    t_contract_billSF = new T_contract_bill(); //水费
                    t_contract_billDF = new T_contract_bill(); //电费
                    //tods 楼栋	房号	起始日期	水表数字	电表数字
                    // 结束日期	结束日期水表数字	结束日期电表数字
                    //1:  楼栋
                    if (tods.size() > 0) {
                        t_contract_billSF.setExt2(tods.get(0).toString());
                        t_contract_billDF.setExt2(tods.get(0).toString());
                    }
                    //2:  房号
                    if (tods.size() > 1) {
                        t_contract_billSF.setExt3 (tods.get(1).toString());
                        t_contract_billDF.setExt3 (tods.get(1).toString());
                    }
                    //3:  起始日期
                    if (tods.size() > 2) {
                        t_contract_billSF.setBillstartdate (tods.get(2).toString());
                        t_contract_billDF.setBillstartdate (tods.get(2).toString());
                    }
                    //4:  水表月初数字
                    if (tods.size() > 3) {
                        t_contract_billSF.setExt5 (tods.get(3).toString());
                    }
                    //5:  电表月初数字
                    if (tods.size() > 4) {
                        t_contract_billDF.setExt5 ( tods.get(4).toString() );
                    }

                    //6:  结束日期
                    if (tods.size() > 5) {
                        t_contract_billSF.setBillenddate(tods.get(5).toString());
                        t_contract_billDF.setBillenddate(tods.get(5).toString());
                    }
                    //7:  结束日期水表月末数字
                    if (tods.size() > 6) {
                        t_contract_billSF.setExt6( tods.get(6).toString());
                    }
                    //8:  结束日期电表月末数字
                    if (tods.size() > 7 && tods.get(7) != null && tods.get(7) != "") {
                        t_contract_billDF.setExt6 (tods.get(7).toString());
                    }

                    t_contract = new T_contract();
                    t_contract.setPageSize(1000);
                    t_contract.setStartNo(0);
                    t_contract.setEndNo(1000);
                    t_contract.setBuildingname(t_contract_billSF.getExt2());
                    t_contract.setHousename(t_contract_billSF.getExt3());
                    t_contract.setContracttype("物业合同");
                    List listcontract = t_contractService.findAll(t_contract);
                    if(null!=listcontract&&listcontract.size()>0){
                        t_contract = (T_contract) listcontract.get(0);
                        //设置水费、电费合同中的单价；
                        t_contract_billSF.setMinprice (t_contract.getEnergytprice()); //物业合同中水费单价字段
                        t_contract_billSF.setTotalprice (countShuifeiTotal(t_contract.getEnergytprice().toString(), t_contract_billSF.getExt6(), t_contract_billSF.getExt5())); //物业合同中水费总金额
                        t_contract_billSF.setContractno(t_contract.getContractno());  //业主/租客
                        t_contract_billSF.setExt1(t_contract.getConpanyname()); // 合同号
                        //应付日期 = 账单到期日 +5天；  //账单到期日 - 水费提前付款天数；
                        t_contract_billSF.setPayday( DateUtil.dateAddDays( DateUtil.format( DateUtil.parseByDefaultDatePattern2(t_contract_billSF.getBillenddate()), "yyyy-MM-dd"), 5 ).toString()); //- t_contract.getEnergyprepaydays()
                        //最后付款日期 = 应付日期  + 7天；
                        t_contract_billSF.setEndday(DateUtil.dateAddDays( DateUtil.format( DateUtil.parseByDefaultDatePattern2(t_contract_billSF.getBillenddate()), "yyyy-MM-dd"), 12 ).toString());

                        t_contract_billDF.setMinprice (t_contract.getContractprice()); //物业合同中电费单价字段
                        t_contract_billDF.setTotalprice (countShuifeiTotal(t_contract.getContractprice().toString(), t_contract_billDF.getExt6(), t_contract_billDF.getExt5())); //物业合同中电费总金额
                        t_contract_billDF.setContractno(t_contract.getContractno());  //业主/租客
                        t_contract_billDF.setExt1(t_contract.getConpanyname()); // 合同号
                        //应付日期 = 账单到期日 +5天；   //账单到期日 - 电费提前付款天数；
                        t_contract_billDF.setPayday( DateUtil.dateAddDays( DateUtil.format( DateUtil.parseByDefaultDatePattern2(t_contract_billDF.getBillenddate()), "yyyy-MM-dd"), 5 ).toString()); //- t_contract.getEnergyprepaydays()
                        //最后付款日期 = 应付日期 + 7天；
                        t_contract_billDF.setEndday(DateUtil.dateAddDays( DateUtil.format( DateUtil.parseByDefaultDatePattern2(t_contract_billDF.getBillenddate()), "yyyy-MM-dd"), 12 ).toString());

                    }

                    t_contract_billSF.setBillno(DateUtil.getCurrDateTime()+new Double(Math.random()).toString().substring(15));
                    t_contract_billSF.setStatus("待生效"); //导入未生效，status="待生效"；
                    t_contract_billSF.setCreattime(DateUtil.getCurrDateTimeStand()); //yyyy-MM-dd HH:mm:ss
                    t_contract_billSF.setCreater(sys_user.getUsername());
                    t_contract_billSF.setBiztype("shuidian" ); // 初始化 Mingpian；
                    t_contract_billSF.setBilltype("水费"); //初始化
                    t_contract_billSF.setSftype("收款");

                    t_contract_billDF.setBillno(DateUtil.getCurrDateTime()+new Double(Math.random()).toString().substring(15));
                    t_contract_billDF.setStatus("待生效"); //导入未生效，status="待生效"；
                    t_contract_billDF.setCreattime(DateUtil.getCurrDateTimeStand()); //yyyy-MM-dd HH:mm:ss
                    t_contract_billDF.setCreater(sys_user.getUsername());
                    t_contract_billDF.setBiztype("shuidian" ); // 初始化 Mingpian；
                    t_contract_billDF.setBilltype("电费"); //初始化
                    t_contract_billDF.setSftype("收款");

                    //插入数据库
                    t_contract_billService.save(t_contract_billSF);
                    t_contract_billService.save(t_contract_billDF);
                }
            }
            //boolean response = customerInfoService.insterCustomerInfo(readExcel, fileName);
            vo.setResult("success");
            vo.setJieguo(true);
            resultString = "success";
        } catch (Exception e) {
            vo.setResult("false");
            vo.setJieguo(false);
            log.error("------readExcel 遇到错误: " + e.toString());
            resultString = "readExcel 遇到错误: " + e.toString();
            e.printStackTrace();
        } finally {
            //return vo;
            request.setAttribute("vo", vo);
            request.setAttribute("resultString", resultString);
            return "business/t_contract_bill/t_contract_billUpload";
        }
    }

    /**
     * 计算 totalprice = 单价 * （当期值 - 上期值）;
     * @param price
     * @param currnum
     * @param prenum
     * @return
     */
    private Double countShuifeiTotal(String price, String currnum, String prenum){
        BigDecimal bprice = new BigDecimal(price);
        BigDecimal bcurrnum = new BigDecimal(currnum);
        BigDecimal bprenum = new BigDecimal(prenum);
        return new Double( bprice.multiply(bcurrnum.subtract(bprenum)).setScale(2).toString() );

    }

    /**
     * 下载快递信息列excel表
     * @param response
     */
    @RequestMapping("/downloadExcelKuaidi")
    //@ResponseBody
    public void downloadExcelKuaidi(String startdate, String enddate, String biztype, String types,  HttpServletResponse response){
        int pageSize = 100000;
        int startIndex = 0;
        T_news t_news = new T_news();
        t_news.setStartNo( startIndex );
        t_news.setPageSize( pageSize );
        t_news.setEndNo( pageSize );
        t_news.setStartdate(startdate);
        t_news.setEnddate(enddate);
        t_news.setBiztype(biztype);
        t_news.setTypes(types);

        String sheetName = "快递申请信息表"+ DateUtil.getCurrDate();
        String fileName = "导出快递申请信息表_"+DateUtil.getCurrDate()+".xls";
        Map<String,Object> excelMap = new HashMap<>();

        try{
            //1.设置Excel表头
            List<String> headerList = new ArrayList<>();
            //寄件物品  寄件日期  寄件人	收件单位	收件人
            // 联系电话	收件详细地址	快递单号	金额(元)	备注
            //	登记人  登记时间	状态
            headerList.add("寄件物品");
            headerList.add("寄件日期");
            headerList.add("寄件人");
            headerList.add("收件单位");
            headerList.add("收件人");

            headerList.add("联系电话");
            headerList.add("收件详细地址");
            headerList.add("快递单号");
            headerList.add("金额(元)");
            headerList.add("备注");

            headerList.add("登记人");
            headerList.add("登记时间");
            headerList.add("状态");
            excelMap.put("header",headerList);

            //2.是否需要生成序号，序号从1开始(true-生成序号 false-不生成序)
            excelMap.put("isSerial",false);

            //3.sheet名
            excelMap.put("sheetName",sheetName);

            //4.需要放入Excel中的数据
            Map<String,Object> map = new HashMap<>();
            //map.put("gender","男");
            List<T_news> rows  = t_newsService.findAll(t_news);
            List<List<String>> data= new ArrayList<>();
            //SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for (T_news t_news1:rows){
                //所有的数据顺序必须和表头一一对应
                //list存放每一行的数据（让所有的数据类型都转换成String，这样就无需担心Excel表格中数据不对）
                List<String> list = new ArrayList<>();
                list.add(String.valueOf(t_news1.getTitle()));
                list.add(String.valueOf(t_news1.getNewdate()));
                list.add(String.valueOf(t_news1.getNewfrom()));
                list.add(String.valueOf(t_news1.getExt1()));
                list.add(String.valueOf(t_news1.getExt2()));

                list.add(String.valueOf(t_news1.getExt8()));
                list.add(String.valueOf(t_news1.getExt3()));
                list.add(String.valueOf(t_news1.getExt4()));
                list.add(String.valueOf(t_news1.getExt5()));
                list.add(String.valueOf(nullToString(t_news1.getExt6())));

                list.add(String.valueOf(nullToString(t_news1.getCreater())));
                list.add(String.valueOf(nullToString(t_news1.getCreattime())));
                list.add(String.valueOf(nullToString(t_news1.getProcess_status())));

                //data存放全部的数据
                data.add(list);
            }
            excelMap.put("data",data);

            //调用下载excel方法；
            HandExcelleFile.downloadExcel(excelMap, fileName, response );

        } catch (Exception e) {
            e.printStackTrace();
            log.error("------query t_orderList error:"+e);
        }
        //return "down excel";
    }

    /**
     * 下载库存信息列excel表
     * @param response
     */
    @RequestMapping("/downloadExcelKucun")
    //@ResponseBody
    public void downloadExcelKucun(String kucuntype ,HttpServletResponse response){
        int pageSize = 100000;
        int startIndex = 0;
        T_order_goods t_order_goods = new T_order_goods();
        List<T_order> findAll = null;
        t_order_goods.setStartNo( startIndex );
        t_order_goods.setPageSize( pageSize );
        t_order_goods.setEndNo( pageSize );
        t_order_goods.setKucuntype(kucuntype);
        String sheetName = "库存信息表"+ DateUtil.getCurrDate();
        String fileName = "导出库存信息表_"+DateUtil.getCurrDate()+".xls";
        Map<String,Object> excelMap = new HashMap<>();

        try{
            //1.设置Excel表头
            List<String> headerList = new ArrayList<>();
            //仓库	类别	物料名称编号	规格型号	初始库存数量
            // 当前库存数量	单位	供应商	批次号	创建者
            //	创建时间  更新者	更新时间	状态
            headerList.add("仓库");
            headerList.add("类别");
            headerList.add("物料名称编号");
            headerList.add("规格型号");
            headerList.add("初始库存数量");

            headerList.add("当前库存数量");
            headerList.add("单位");
            headerList.add("供应商");
            headerList.add("批次号");
            headerList.add("创建者");

            headerList.add("创建时间");
            headerList.add("更新者");
            headerList.add("更新时间");
            headerList.add("状态");
            excelMap.put("header",headerList);

            //2.是否需要生成序号，序号从1开始(true-生成序号 false-不生成序)
            excelMap.put("isSerial",false);

            //3.sheet名
            excelMap.put("sheetName",sheetName);

            //4.需要放入Excel中的数据
            Map<String,Object> map = new HashMap<>();
            //map.put("gender","男");
            List<T_order_goods> rows  = t_order_goodsService.findAll(t_order_goods);
            List<List<String>> data= new ArrayList<>();
            //SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for (T_order_goods t_order_goods1:rows){
                //所有的数据顺序必须和表头一一对应
                //list存放每一行的数据（让所有的数据类型都转换成String，这样就无需担心Excel表格中数据不对）
                List<String> list = new ArrayList<>();
                list.add(String.valueOf(t_order_goods1.getBuyer_company_name()));
                list.add(String.valueOf(t_order_goods1.getSaller_tel()));
                list.add(String.valueOf(t_order_goods1.getBuyer_tel()));
                list.add(String.valueOf(t_order_goods1.getSaller_company_address()));
                list.add(String.valueOf(t_order_goods1.getOld_price()));

                list.add(String.valueOf(t_order_goods1.getGood_num()));
                list.add(String.valueOf(t_order_goods1.getExt2()));
                list.add(String.valueOf(t_order_goods1.getSaller_company_name()));
                list.add(String.valueOf(t_order_goods1.getExt1()));
                list.add(String.valueOf(nullToString(t_order_goods1.getCreater())));

                list.add(String.valueOf(nullToString(t_order_goods1.getCreattime())));
                list.add(String.valueOf(nullToString(t_order_goods1.getUpdater())));
                list.add(String.valueOf(nullToString(t_order_goods1.getUpdatetime())));
                list.add(String.valueOf(t_order_goods1.getStatus()));
                //data存放全部的数据
                data.add(list);
            }
            excelMap.put("data",data);

            //调用下载excel方法；
            HandExcelleFile.downloadExcel(excelMap, fileName, response );

        } catch (Exception e) {
            e.printStackTrace();
            log.error("------query t_orderList error:"+e);
        }
        //return "down excel";
    }

    public String nullToString(Object obj){
        if(null != obj) {
            return obj.toString();
        }
        else{
            return "";
        }
    }
}